package com.ruoyi.system.mapper;

import java.util.List;
import com.ruoyi.system.domain.MdjfCaseMain;
import com.ruoyi.system.domain.vo.*;
import io.lettuce.core.dynamic.annotation.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

/**
 * 矛盾纠纷--案件主事件Mapper接口
 * 
 * @author ruoyi
 * @date 2025-03-28
 */
public interface MdjfCaseMainMapper 
{
    /**
     * 查询矛盾纠纷--案件主事件
     * 
     * @param caseMainId 矛盾纠纷--案件主事件主键
     * @return 矛盾纠纷--案件主事件
     */
    public MdjfCaseMain selectMdjfCaseMainByCaseMainId(Long caseMainId);

    /**
     * 查询矛盾纠纷--案件主事件列表
     * 
     * @param mdjfCaseMain 矛盾纠纷--案件主事件
     * @return 矛盾纠纷--案件主事件集合
     */
    public List<MdjfCaseMain> selectMdjfCaseMainList(MdjfCaseMain mdjfCaseMain);


    public List<MdjfCaseMain> selectMdjfCaseMainListNoAuth(MdjfCaseMain mdjfCaseMain);

    /**
     * 新增矛盾纠纷--案件主事件
     * 
     * @param mdjfCaseMain 矛盾纠纷--案件主事件
     * @return 结果
     */
    public int insertMdjfCaseMain(MdjfCaseMain mdjfCaseMain);

    /**
     * 修改矛盾纠纷--案件主事件
     * 
     * @param mdjfCaseMain 矛盾纠纷--案件主事件
     * @return 结果
     */
    public int updateMdjfCaseMain(MdjfCaseMain mdjfCaseMain);

    /**
     * 删除矛盾纠纷--案件主事件
     * 
     * @param caseMainId 矛盾纠纷--案件主事件主键
     * @return 结果
     */
    public int deleteMdjfCaseMainByCaseMainId(Long caseMainId);

    /**
     * 批量删除矛盾纠纷--案件主事件
     * 
     * @param caseMainIds 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteMdjfCaseMainByCaseMainIds(Long[] caseMainIds);

    /**
     * 批量删除矛盾纠纷--案件主事件
     *
     * @param caseMainIds 矛盾纠纷--案件主事件主键
     * @return 结果
     */
    public int batchUpdateStatus(Long[] caseMainIds);

    @Select("SELECT \n" +
            "    month_list.month AS stat_month,\n" +
            "    COALESCE(dept_list.dept_name, '其他') AS dept_name,\n" +
            "    COALESCE(data.case_count, 0) AS case_count,\n" +
            "    COALESCE(total.monthly_total, 0) AS monthly_total\n" +
            "FROM \n" +
            "    -- 生成1-12月虚拟表\n" +
            "    (\n" +
            "        SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 \n" +
            "        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 \n" +
            "        UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12\n" +
            "    ) AS month_list\n" +
            "CROSS JOIN \n" +
            "    -- 部门表 + 虚拟\"其他\"部门\n" +
            "    (\n" +
            "        SELECT dept_id, dept_name FROM sys_dept\n" +
            "        UNION ALL\n" +
            "        SELECT NULL AS dept_id, '其他' AS dept_name\n" +
            "    ) AS dept_list\n" +
            "LEFT JOIN \n" +
            "    -- 按月份+部门统计案件数（处理未关联部门为\"其他\"）\n" +
            "    (\n" +
            "        SELECT \n" +
            "            MONTH(c.create_time) AS case_month,\n" +
            "            COALESCE(d.dept_name, '其他') AS dept_name,\n" +
            "            COUNT(*) AS case_count\n" +
            "        FROM mdjf_case_main c\n" +
            "        LEFT JOIN sys_dept d ON c.checkin_dept_id = d.dept_id\n" +
            "        WHERE YEAR(c.create_time) = #{targetYear}  AND c.status = 0\n" +
            "        GROUP BY MONTH(c.create_time), COALESCE(d.dept_name, '其他')\n" +
            "    ) AS data \n" +
            "    ON month_list.month = data.case_month \n" +
            "    AND dept_list.dept_name = data.dept_name\n" +
            "LEFT JOIN \n" +
            "    -- 按月统计总案件数（含无数据月份补零）\n" +
            "    (\n" +
            "        SELECT \n" +
            "            MONTH(create_time) AS total_month,\n" +
            "            COUNT(*) AS monthly_total\n" +
            "        FROM mdjf_case_main\n" +
            "        WHERE YEAR(create_time) = #{targetYear}  AND status = 0\n" +
            "        GROUP BY MONTH(create_time)\n" +
            "    ) AS total \n" +
            "    ON month_list.month = total.total_month\n" +
            "ORDER BY \n" +
            "    stat_month, \n" +
            "    case_count DESC;")
    @Results({
            @Result(property = "statMonth", column = "stat_month"),
            @Result(property = "deptName", column = "dept_name"),
            @Result(property = "caseCount", column = "case_count"),
            @Result(property = "monthlyTotal", column = "monthly_total")
    })
    List<MonthDeptStat> selectYearStat(@Param("targetYear") Integer year);


    @Select("SELECT \n" +
            "    COALESCE(dept_list.dept_name, '其他') AS dept_name,\n" +
            "    COALESCE(data.dept_count, 0) AS dept_count,\n" +
            "    COALESCE(total.total_count, 0) AS total_count\n" +
            "FROM \n" +
            "    -- 部门表 + 虚拟\"其他\"部门\n" +
            "    (\n" +
            "        SELECT dept_id, dept_name FROM sys_dept\n" +
            "        UNION ALL\n" +
            "        SELECT NULL AS dept_id, '其他' AS dept_name\n" +
            "    ) AS dept_list\n" +
            "LEFT JOIN \n" +
            "    -- 按部门和日期统计数据量\n" +
            "    (\n" +
            "        SELECT \n" +
            "            COALESCE(d.dept_name, '其他') AS dept_name,\n" +
            "            COUNT(*) AS dept_count\n" +
            "        FROM mdjf_case_main c\n" +
            "        LEFT JOIN sys_dept d ON c.checkin_dept_id = d.dept_id\n" +
            "        WHERE DATE(c.create_time) = #{day}  -- 指定查询日期\n" +
            "        GROUP BY COALESCE(d.dept_name, '其他')\n" +
            "    ) AS data \n" +
            "    ON dept_list.dept_name = data.dept_name\n" +
            "LEFT JOIN \n" +
            "    -- 当天总数据量\n" +
            "    (\n" +
            "        SELECT COUNT(*) AS total_count\n" +
            "        FROM mdjf_case_main\n" +
            "        WHERE DATE(create_time) = #{day}  -- 指定查询日期\n" +
            "    ) AS total \n" +
            "    ON 1=1  -- 强制关联总数据\n" +
            "ORDER BY \n" +
            "    dept_count DESC;")
    @Results({
            @Result(property = "totalCount", column = "total_count"),
            @Result(property = "deptName", column = "dept_name"),
            @Result(property = "deptCount", column = "dept_count")
    })
    List<DayDeptStat> getDayDeptStat(@Param("day") String day);



    List<ReTrend> getRecentTrend(Integer type);

    List<ReTrend> CasesGrowResult(Integer type);

    List<ReTrend> casesDispose(Integer type);


    Integer handleAnalytics(String districtStatus);

    @Select("SELECT \n" +
            "    a.checkin_dept_id AS deptId,  \n" +
            "    COUNT(*) AS totalCount,  \n" +
            "    SUM(CASE WHEN a.dispose_status IN (0, 1) THEN 1 ELSE 0 END) AS pendingCount,  \n" +
            "    SUM(CASE WHEN a.dispose_status = 2 THEN 1 ELSE 0 END) AS processingCount,  \n" +
            "    SUM(CASE WHEN a.dispose_status = 3 THEN 1 ELSE 0 END) AS completedCount,  \n" +
            "     MAX(b.dept_name) AS deptName,\n" +
            "     MAX(DATE(a.create_time)) AS today " +
            "FROM \n" +
            "    mdjf_case_main a \n" +
            "LEFT JOIN sys_dept b ON a.checkin_dept_id = b.dept_id\n" +
            "WHERE \n" +
            "    a.status = 0 \n" +
            "    AND DATE(a.create_time) = #{day}   -- 筛选今日数据（方法1）\n" +
            "    -- 或 AND a.create_time >= CURDATE() AND a.create_time < CURDATE() + INTERVAL 1 DAY  -- 方法2（性能更优）\n" +
            "GROUP BY \n" +
            "    a.checkin_dept_id\n" +
            "ORDER BY \n" +
            "    a.checkin_dept_id;")
    List<CaseHandlingResult> getCaseHandling(String day);


    @Select("SELECT\n" +
            "\ta.dict_code AS dictCode,a.dict_label AS dictLabel,\n" +
            "\tIFNULL(b.count,0) AS caseCount,\n" +
            "\tb.checkin_township_id AS checkinTownshipId\n" +
            "FROM\n" +
            "\tsys_dict_data a\n" +
            "\tLEFT JOIN ( SELECT count(1) AS count, checkin_township_id FROM mdjf_case_main WHERE STATUS = 0 GROUP BY checkin_township_id ) b ON a.dict_code = b.checkin_township_id \n" +
            "WHERE\n" +
            "\ta.dict_type = 'checkin_township' \n" +
            "\tAND a.`status` = 0")
    List<CasesRegionResult> CasesRegion();




}
